﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;
/// <summary>
/// Summary description for OleDbConnectionProvider
/// </summary>
public class OleDbConnectionProvider
{
    OleDbConnection oledbConn;
    OleDbCommand oledbCommand;
    private String version;

    private String GetFileVersion(string dataSourcePath)
    {
        string fileExt = System.IO.Path.GetExtension(dataSourcePath);
        return (fileExt == ".xls") ? "4.0" : "12.0";
    }



    public string GetConnectionString(string dataSourcePath)
    {
        version = GetFileVersion(dataSourcePath);

        if (string.Compare(version.Trim(), "12.0", true) == 0)
        {
            return "Provider=Microsoft.ACE.OLEDB.12.0;"
                 + "Data Source=" + dataSourcePath
                + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
        }
        else if (string.Compare(version.Trim(), "4.0", true) == 0)
        {
            return "Provider=Microsoft.Jet.OLEDB.4.0;"
                 + "Data Source=" + dataSourcePath
                + ";" + "Extended Properties=Excel 8.0;";
        }
        return "Provider=Microsoft.ACE.OLEDB.12.0;"
                 + "Data Source=" + dataSourcePath
                + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";

    }

    public OleDbConnectionProvider(string dataSourcePath, string command)
    {
        string connString = GetConnectionString(dataSourcePath);
        oledbConn = new OleDbConnection(connString);
        oledbCommand = new OleDbCommand(command, oledbConn);
        try
        {
            // Open connection
            oledbConn.Open();
        }
        catch (Exception ex)
        {
            // Close connection if it is open
            Close();
            throw ex;
        }
    }


    public OleDbConnectionProvider(string dataSourcePath)
    {
        string connString = GetConnectionString(dataSourcePath);
        oledbConn = new OleDbConnection(connString);
        try
        {
            // Open connection
            oledbConn.Open();
        }
        catch (Exception ex)
        {
            // Close connection if it is open
            Close();
            throw ex;
        }
    }


    /// <summary>
    /// Gets the dataset container a table (worksheet) specified
    /// </summary>
    /// <param name="tableName">name of the table(worksheet) that should be read.</param>
    /// <returns>returns <c>DataSet</c> that contains a single table (worksheet)</returns>
    public DataSet GetDataSetWithSingleTable(string tableName)
    {

        // Create new OleDbDataAdapter
        OleDbDataAdapter oleda = new OleDbDataAdapter();

        oleda.SelectCommand = oledbCommand;

        // Create a DataSet which will hold the data extracted from the worksheet.
        DataSet ds = new DataSet();

        // Fill the DataSet from the data extracted from the worksheet.
        oleda.Fill(ds, tableName);

        return ds;
    }


    /// <summary>
    /// Encapsulates the close connection operation for a oledb connection
    /// </summary>
    public void Close()
    {
        // Close connection if it is open
        if (oledbConn.State == ConnectionState.Open)
        {

            oledbConn.Close();
            oledbCommand = null;
        }
    }

    /// <summary>
    /// Checks if a sheet name is present in a excel workbook
    /// </summary>
    /// <param name="sheetName">sheet name</param>
    /// <returns>returns <c>true</c> if the excel workbook has a the sheet name. <c>false</c> if not.</returns>
    public bool WorkBookHasSheet(string sheetName)
    {
        /* open oledb connection to the excel workbook and get worksheet information*/
        DataTable dtSheet = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        Close();
        
        List<string> listSheet = new List<string>();
        bool result = false;
        foreach (DataRow drSheet in dtSheet.Rows)
        {
            if (drSheet["TABLE_NAME"].ToString().Contains("$") && string.Compare(drSheet["TABLE_NAME"].ToString(), sheetName, true) == 0)//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
            {
                result = true;
                break;
            }
        }
        return result;
    }
}